1. Retrieve all information about the Lecturers who work in College 'KSIT10' and teach Subject ID (SID) '24SQL'.
SELECT * FROM Lecturers WHERE College = 'KSIT10' AND SID = '24SQL'
2. The college is organizing a special workshop for students with USNs '1SW24CS001' or '1SW24IS001'. Write a query to find the first names and branches of these students using IN Operator.
SELECT First_name,Branch FROM Students WHERE Usn IN ('1SW24CS001', '1SW24IS001');
3. The administration wants to invite lecturers from the colleges 'KSIT10' or 'SIT10' for a faculty development program. Write a query to list the first names and designations of these lecturers using IN Operator.
SELECT First_name,Designation FROM Lecturers WHERE College IN ('KSIT10','SIT10');
4. Retrieve all the information from students marks where Subject CGPA is not greater than 7.5 and Test 1 marks are not greater than or equal to 25. (Using NOT Clause)
SELECT * FROM Marks WHERE NOT (Subject_CGPA > 7.5) AND NOT (Test1 >= 25);
5. Retrieve all information about the Student's Marks who have scored at least 20 in Test1 but less than 20 in other two tests.
SELECT * FROM Marks WHERE Test1 >= 20 AND Test2 < 20 AND Test3 < 20;
6. Retrieve all information about Lecturers who are Designated as 'HOD' or teach in Branch 'CSE3'
SELECT * FROM Lecturers WHERE Designation = 'HOD' AND Branch = 'CSE3'
7. Retrieve all information for the companies that offer Package Greater than 5 Lakh or the Number of Students Placed is at least 2.
SELECT * FROM Companies WHERE Package > 500000 OR Students_placed >=2
8. A scholarship is being offered to students who have a CGPA of 7.8 or 9. Write a query to find the USN and first name of these students using IN Operator.
SELECT Usn,First_name FROM Students WHERE CGPA IN (7.8,9)
9. Retrieve Student Usn, Name, Company ID (CID), and Package of all the Students who either have a job role of 'Data Analyst' with a package above 5 lakhs or have a CGPA of at least 9.
SELECT Usn,First_name,CID,Package FROM Students WHERE (Job_role='Data Analyst' AND Package >500000) OR cgpa >= 9;
10. Retrieve the Names and Packages of the Students who have a job role of 'Data Analyst' with a package above 6 lakhs or joined after August 2024.
SELECT First_name,Package FROM Students WHERE (Job_role = 'Data Analyst' AND Package>600000) OR DOJ >'2024-08-31';
11. Retrieve the First_name, College, and Package of all students who have a Package greater than 5 Lakh, are in the 'Data Analyst' job role, and joined the college (DOJ) from August 2024.
SELECT First_name,College,Package FROM Students WHERE Job_role ='Data Analyst' AND PACKAGE > 500000 AND DOJ >= '2024-08-01';
12. Retrieve all information from Students Marks who have scored less than 20 on any of the Tests, but have Scored at least 7 Subject CGPA.
SELECT * FROM Marks WHERE (Test1 < 20 OR Test2 < 20 OR Test3 < 20) AND Subject_CGPA >= 7;
13. Retrieve Last 5 Students USN, Subject ID (SID), and Subject CGPA who have the least Subject CGPA.
SELECT Usn,SID,Subject_CGPA FROM Marks ORDER BY Subject_CGPA ASC LIMIT 5;
14. Retrieve the USN, Subject ID (SID), and CGPA of all students who have either Scored at least 30 in Test1 for Subject ID '24SQL', or Scored at least 25 in Test2 for Subject ID '24HTML', but whose Subject CGPA is not greater than 9.
SELECT Usn,SID,Subject_CGPA FROM Marks WHERE ((SID = '24SQL' AND Test1 >= 30) OR SID = '24HTML' AND Test2 >= 25) AND Subject_CGPA <=9;
15. Retrieve the First_name, Branch, and CGPA of all male students who joined the college (DOJ) from August 2024, have a CGPA greater than 7, and are either in Branch 'CSE1' or 'CSE2'.
SELECT First_name, Branch, CGPA FROM Students WHERE DOJ >= '2024-08-01' AND CGPA > 7 AND Branch IN ('CSE1', 'CSE2') AND Gender = 'M'